package com.group18.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.group18.db.DBUtil;
import com.group18.entity.AccountUser;



/**
 * User dao类
 * @author Nicky
 *
 */
public class AccountUserDao extends BaseDao{
	
	/**
	 * 模糊搜索,并按时间降序排序
	 * @param name
	 * @return
	 */
	public List<AccountUser> SelectAccountByText(String text) {
		List<AccountUser> list = new ArrayList<AccountUser>();
		try {
			connection = DBUtil.getConnection();
			
			preparedStatement = connection.prepareStatement("select * from account where username like ? or state like ? or label like ? or statue like ? or money like ? or date like ? ORDER BY date DESC");
			for(int i = 1; i <= 6; i++) {
				//搜索6个占位符
				preparedStatement.setString(i, "%"+text+"%");
			}
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				AccountUser au = new AccountUser();
				au.setId(resultSet.getInt("id"));
				au.setUsername(resultSet.getString("username"));
				au.setState(resultSet.getString("state"));
				au.setLabel(resultSet.getString("label"));
				au.setStatue(resultSet.getString("statue"));
				au.setMoney(resultSet.getDouble("money"));
				au.setDate(resultSet.getString("date"));
				au.setFamily_id(resultSet.getInt("family_id"));
				au.setMember_id(resultSet.getInt("member_id"));
				list.add(au);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closed();
		}
		return list;
	}
	
	/**
	 * 添加收支
	 * @param u
	 * @return	返回修改数据库条数0 失败 1成功
	 */
	public int addAccount(AccountUser u) {
		int count = 0;
		try {
			connection = DBUtil.getConnection();
			String sql = "insert into account(username,state,label,statue,money,date,family_id,member_id) values(?,?,?,?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, u.getUsername());
			preparedStatement.setString(2, u.getState());
			preparedStatement.setString(3, u.getLabel());
			preparedStatement.setString(4, u.getStatue());
			preparedStatement.setDouble(5, u.getMoney());
			preparedStatement.setString(6, u.getDate());
			preparedStatement.setInt(7, u.getFamily_id());
			preparedStatement.setInt(8, u.getMember_id());
			count = preparedStatement.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			closed();
		}
		return count;
	}
	
	/**
	 * 修改收支
	 * @param u
	 * @return	修改数据条数
	 */
	public int upDataAccount(AccountUser u) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("update account set username = ?, state = ?, label = ?, statue = ?, money = ?, date = ?, family_id = ?, member_id = ? where id = ?");
			preparedStatement.setString(1, u.getUsername());
			preparedStatement.setString(2, u.getState());
			preparedStatement.setString(3, u.getLabel());
			preparedStatement.setString(4, u.getStatue());
			preparedStatement.setDouble(5, u.getMoney());
			preparedStatement.setString(6, u.getDate());
			preparedStatement.setInt(7, u.getFamily_id());
			preparedStatement.setInt(8, u.getMember_id());
			preparedStatement.setInt(9, u.getId());
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 修改收支记录人
	 * @param u
	 * @return
	 */
	public int updateAccountUsername(AccountUser u) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("update account set username = ?,family_id = ? where member_id = ?");
			preparedStatement.setString(1, u.getUsername());
			preparedStatement.setInt(2, u.getFamily_id());
			preparedStatement.setInt(3, u.getMember_id());
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 判断用户名对应的数据是否存在
	 * @param u
	 * @return	true 存在，false 不存在
	 */
	public boolean isExist(AccountUser u) {
		boolean flag = false;
		try {
			int count = 0;
			connection = DBUtil.getConnection();
			String sql = "select count(*) as count from account where name=?";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				count = resultSet.getInt("count");
			}
			if(count != 0) {
				flag = true;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			closed();
		}
		return flag;
	}
	
	/**
	 * 查询家庭所有收支,并按时间降序排序
	 * @return
	 */
	public List<AccountUser> selectAllAccountByFamily_id(AccountUser u) {
		
		List<AccountUser> uList = new ArrayList<>();
		
		try {
			connection = DBUtil.getConnection();
			//若要升序排序：DESC改为ASC
			preparedStatement = connection.prepareStatement("select * from account where family_id = ? ORDER BY date DESC");
			preparedStatement.setInt(1, u.getFamily_id());
			resultSet =	preparedStatement.executeQuery();
			while(resultSet.next()) {
				AccountUser au = new AccountUser();
				au.setId(resultSet.getInt("id"));
				au.setUsername(resultSet.getString("username"));
				au.setState(resultSet.getString("state"));
				au.setLabel(resultSet.getString("label"));
				au.setStatue(resultSet.getString("statue"));
				au.setMoney(resultSet.getDouble("money"));
				au.setDate(resultSet.getString("date"));
				au.setFamily_id(resultSet.getInt("family_id"));
				au.setMember_id(resultSet.getInt("member_id"));
				uList.add(au);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			closed();
		}
		return uList;
	}
	
	/**
	 * 根据家庭id查询家庭的所有收支和支出
	 * @param family
	 * @return
	 */
	public int selectAccountByFamily_id(int family_id) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("select count(*) from account where family_id = ?");
			preparedStatement.setInt(1, family_id);
			resultSet =	preparedStatement.executeQuery();
			while(resultSet.next()) {
				i = resultSet.getInt("count");
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			closed();
		}
		return i;
	}
	
	/**
	 * 删除某个家庭的所有收支
	 * @param family_id	家庭id
	 * @return
	 */
	public int deleteAccountByFamily_id(int family_id) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("delete from account where family_id = ?");
			preparedStatement.setInt(1, family_id);
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closed();
		}
		return i;
	}
	
	/**
	 * 删除某个家庭成员所有收支
	 * @param member_id
	 * @return
	 */
	public int deleteAccountByMember_id(int member_id) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("delete from account where member_id = ?");
			preparedStatement.setInt(1, member_id);
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closed();
		}
		return i;
	}
	
	/**
	 * 删除收支
	 * @param id
	 * 				要删除的id
	 * @return
	 * 				删除数据的条数
	 */
	public int deleteByID(int id) {
		int i = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement("delete from account where id = ?");
			preparedStatement.setInt(1, id);
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closed();
		}
		return i;
	}
	
}
